package com.cpi.cornpp.costelement.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

import com.cpi.cornpp.common.exception.CornPPException;
import com.cpi.cornpp.costelement.dto.CostElementDTO;
import com.cpi.cornpp.costelement.exception.CostElementException;
import com.cpi.cornpp.costelement.services.CachedCostElementList;
import com.cs.common.pool.DBDAO;
import com.cs.common.pool.exception.PoolException;
/**
 * Data Access of Cost Element
 * @author Alekx Aguilar
 *
 */
public class CostElementDAO extends DBDAO {
	/** Datasource name */
	private static final String dsName = "cornpp";//$NON-NLS-1$
	/**SQL Get the cost element information using an exists cost element code*/
	private static final String SQL_VALIDATE_EXISTS_COST_ELEMENT = "SELECT * FROM COSTS_ELEMENTS WHERE SHORT_NAME = ?";//$NON-NLS-1$
	/**SQL Get the cost element information using an exists cost element code*/
	private static final String SQL_COST_ELEMENT_BY_ID = "SELECT * FROM COSTS_ELEMENTS WHERE cost_element_id = ?";//$NON-NLS-1$
	/**SQL Insert new cost element*/
	private static final String SQL_INSERT_COST_ELEMENT ="INSERT INTO costs_elements (short_name,description) VALUES (UPPER(?),?)";//$NON-NLS-1$
	/**SQL Select all cost elements*/
	private static final String SQL_SELECT_ALL_COST_ELEMENTS ="SELECT cost_element_id, SHORT_NAME,DESCRIPTION, type_id FROM COSTS_ELEMENTS where type_id <> 2";//$NON-NLS-1$
	/**SQL Select missed cost elements*/
	private static final String SQL_SELECT_MISSED_COST_ELEMENTS ="SELECT COSTS_ELEMENTS.cost_element_id, SHORT_NAME,DESCRIPTION, type_id FROM COSTS_ELEMENTS left join purchases_orders_costs_versions on purchases_orders_costs_versions.purchase_order_version_id = ? and purchases_orders_costs_versions.cost_element_id = COSTS_ELEMENTS.cost_element_id where type_id <> 2 and purchases_orders_costs_versions.cost_element_id is null";//$NON-NLS-1$
	/**SQL Edit Cost Element*/
	private static final String SQL_EDIT_COST_ELEMENT = "UPDATE COSTS_ELEMENTS SET SHORT_NAME = UPPER(?) , DESCRIPTION =?  WHERE cost_element_id =?";//$NON-NLS-1$
	/**SQL Delete Cost Element*/
	private static final String SQL_DELETE_COST_ELEMENT = "DELETE COSTS_ELEMENTS WHERE cost_element_id = ? ";//$NON-NLS-1$
	/**Error cost element already exists in the data base*/
	private static final String ERROR_CODE_COST_ALREADY_EXIST = "error.costelement.alreadyexists";//$NON-NLS-1$
	
	private static final String SQL_DELETE_TRANSPORT_COST_ELEMENT = "delete from transports_costs where cost_element_id = ?";//$NON-NLS-1$
	
	/**
	 * Validate if the cost element exists in the data base
	 * @param costElementCode
	 * @return
	 */
	public boolean existCostElement(String costElementCode ) throws CostElementException {
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try{
				pstm = prepareSQL(SQL_VALIDATE_EXISTS_COST_ELEMENT, dsName);
				pstm.setString(1, costElementCode);
				rs = executeQuery(pstm);
				return rs.next() ? true : false;
				
		}catch (PoolException poolException){
			CostElementException costElementException = null;
			poolException.printStackTrace(System.out);
			costElementException = new CostElementException (poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_SELECT);
			throw costElementException;
		} catch(SQLException sqle){
			CostElementException costElementException = null;
			sqle.printStackTrace(System.out);
			costElementException = new CostElementException (sqle, CornPPException.LAYER_DAO,CornPPException.ACTION_SELECT);
			throw costElementException;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}	
	}
	
	
	/**
	 * 
	 * Obtener un costElement basado en su shortname 
	 */
	public CostElementDTO getById(int id) throws Exception {
		PreparedStatement pstm =  null;
		CostElementDTO costElementDTO = null;		
		ResultSet rs = null;
		try {
			
			costElementDTO = new CostElementDTO();
			pstm = prepareSQL(SQL_COST_ELEMENT_BY_ID, dsName);
			pstm.setInt(1, id);
			rs = executeQuery(pstm);
			
			if (rs.next()){
				costElementDTO = new CostElementDTO();
				
				costElementDTO.setCostElementID(rs.getInt(1));
				costElementDTO.setCostElementCode(rs.getString(2));
				costElementDTO.setCostElementDescription(rs.getString(3));				
				
			}			
		}
		catch (SQLException sqlException){
			CostElementException costElementException = null;
			sqlException.printStackTrace(System.out);
			costElementException = new CostElementException (sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_SELECT);
			throw costElementException;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}
		return costElementDTO;
	}
	
	
	/**
	 * Insert new cost element
	 * @param costElementDTO
	 * @return
	 */
	public int insertCostElement( CostElementDTO costElementDTO) throws CostElementException{
		PreparedStatement pstm = null;
		int resultIns=0;
		int costElementId = -1;
		try{
				if (existCostElement(costElementDTO.getCostElementCode())){
					CostElementException costElementException = null;
					costElementException = new CostElementException (ERROR_CODE_COST_ALREADY_EXIST,CornPPException.LAYER_DAO,CornPPException.ACTION_INSERT);
					throw costElementException;						
				}
				pstm = prepareSQL(SQL_INSERT_COST_ELEMENT, dsName);
				pstm.setString(1, costElementDTO.getCostElementCode());
				pstm.setString(2, costElementDTO.getCostElementDescription());
				resultIns = executeInsert(pstm);
				if (resultIns <= 0 ) {
					CostElementException costElementException = null;
					costElementException = new CostElementException (MSG_NO_INSERT,CornPPException.LAYER_DAO,CornPPException.ACTION_INSERT);
					throw costElementException;				
				}
				
				costElementId = Integer.parseInt(getInsertedId(pstm.getConnection()));
				CachedCostElementList.addCostElement(costElementDTO);
				
		}catch (PoolException poolException){
			CostElementException costElementException = null;
			poolException.printStackTrace(System.out);
			costElementException = new CostElementException (poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_INSERT);
			throw costElementException;
		} catch(SQLException sqle){
			CostElementException costElementException = null;
			sqle.printStackTrace(System.out);
			costElementException = new CostElementException (sqle, CornPPException.LAYER_DAO,CornPPException.ACTION_INSERT);
			throw costElementException;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}
		return costElementId;
	}
	/**
	 * Edit cost element
	 * @param costElementDTO
	 * @return  TRUE : Record edited correctly , FALSE: edit fails 
	 * @throws CostElementException
	 */
	public void editCostElement(CostElementDTO costElementDTO) throws CostElementException{
		PreparedStatement preparedStatement = null;
		int resultIns=0;
		try{
				preparedStatement = prepareSQL(SQL_EDIT_COST_ELEMENT, dsName);
				preparedStatement.setString(1, costElementDTO.getCostElementCode());
				preparedStatement.setString(2, costElementDTO.getCostElementDescription());
				preparedStatement.setInt(3, costElementDTO.getCostElementID());
				resultIns = executeUpdate(preparedStatement);
				if (resultIns <= 0 ) {
					CostElementException costElementException = null;
					costElementException = new CostElementException (MSG_NO_UPDATE,CornPPException.LAYER_DAO,CornPPException.ACTION_UPDATE);
					throw costElementException;				
				}
				CachedCostElementList.updateCostElement(costElementDTO);
		}catch (PoolException poolException){
			CostElementException costElementException = null;
			poolException.printStackTrace(System.out);
			costElementException = new CostElementException (poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_UPDATE);
			throw costElementException;
		} catch(SQLException sqle){
			CostElementException costElementException = null;
			sqle.printStackTrace(System.out);
			costElementException = new CostElementException (sqle, CornPPException.LAYER_DAO,CornPPException.ACTION_UPDATE);
			throw costElementException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}	
	}	
	/**
	 * Delete a Cost Element
	 * @param costElementDTO
	 * @return TRUE : Record deleted , FALSE: deletion fails 
	 * @throws CostElementException
	 */
	public void deleteCostElement(int costElementId) throws CostElementException{
		int deletedRows = 0;
		PreparedStatement preparedStatement = null;
		try{
			preparedStatement = prepareSQL(SQL_DELETE_TRANSPORT_COST_ELEMENT, dsName);
			preparedStatement.setInt(1, costElementId);
			executeUpdate(preparedStatement);
			
			preparedStatement = prepareSQL(SQL_DELETE_COST_ELEMENT, dsName);
			preparedStatement.setInt(1, costElementId);
			deletedRows = executeUpdate(preparedStatement);
			if (deletedRows <= 0 ) {
				CostElementException costElementException = null;
				costElementException = new CostElementException (MSG_NO_DELETE,CornPPException.LAYER_DAO,CornPPException.ACTION_DELETE);
				throw costElementException;
			}
			CostElementDTO costElementDTO = new CostElementDTO();
			costElementDTO.setCostElementID(costElementId);
			CachedCostElementList.removeItem(costElementDTO);
		}catch (PoolException poolException){
			CostElementException costElementException = null;
			poolException.printStackTrace(System.out);
			costElementException = new CostElementException (poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_DELETE);
			throw costElementException;
		} catch(SQLException sqle){
			CostElementException costElementException = null;
			sqle.printStackTrace(System.out);
			costElementException = new CostElementException (sqle, CornPPException.LAYER_DAO,CornPPException.ACTION_DELETE);
			throw costElementException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}	
	}	
	/**
	 * Get all the cost elements
	 * @return
	 * @throws CostElementException
	 */
	public Collection getList(int orderField, boolean ascendingOrder)throws CostElementException{
		StringBuffer sql = null;
		CostElementDTO costElementDTO = null;
		List listResult = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try{
			listResult = new ArrayList(0);
			sql = new StringBuffer(SQL_SELECT_ALL_COST_ELEMENTS);
			sql.append(SQL_ORDER_BY);
			switch (orderField){
				case 1: sql.append(CostElementDTO.SHORT_NAME);
						break;
				case 2: sql.append(CostElementDTO.DESCRIPTION);
						break;
			}
			if (ascendingOrder){
				sql.append(SQL_ASCENDING_ORDER);
			} else {
				sql.append(SQL_DESCENDING_ORDER);
			}
			pstm = prepareSQL(sql.toString(), dsName);
			rs = executeQuery(pstm);
			while (rs.next()){
				costElementDTO = new CostElementDTO();
				costElementDTO.setCostElementID(rs.getInt(1));
				costElementDTO.setCostElementCode(rs.getString(2));
				costElementDTO.setCostElementDescription(rs.getString(3));
				costElementDTO.setTypeId(rs.getInt(4));
				listResult.add(costElementDTO);
			}
		}catch (PoolException poolException){
			CostElementException costElementException = null;
			poolException.printStackTrace(System.out);
			costElementException = new CostElementException (poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw costElementException;
		} catch(SQLException sqle){
			CostElementException costElementException = null;
			sqle.printStackTrace(System.out);
			costElementException = new CostElementException (sqle, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw costElementException;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}	
		return listResult;		
	}
	
	
	/**
	 * Get all the cost elements
	 * @return
	 * @throws CostElementException
	 */
	public Collection getList()throws CostElementException{
		StringBuffer sql = null;
		CostElementDTO costElementDTO = null;
		List listResult = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try{
			listResult = new ArrayList(0);
			sql = new StringBuffer(SQL_SELECT_ALL_COST_ELEMENTS);
		
			pstm = prepareSQL(sql.toString(), dsName);
			rs = executeQuery(pstm);
			while (rs.next()){
				costElementDTO = new CostElementDTO();
				costElementDTO.setCostElementID(rs.getInt(1));
				costElementDTO.setCostElementCode(rs.getString(2));
				costElementDTO.setCostElementDescription(rs.getString(3));
				listResult.add(costElementDTO);
			}
		}catch (PoolException poolException){
			CostElementException costElementException = null;
			poolException.printStackTrace(System.out);
			costElementException = new CostElementException (poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw costElementException;
		} catch(SQLException sqle){
			CostElementException costElementException = null;
			sqle.printStackTrace(System.out);
			costElementException = new CostElementException (sqle, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw costElementException;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}	
		
		return listResult;		
	}
	
	public Collection getList(int purchaseOrderVersionId)throws CostElementException{
		StringBuffer sql = null;
		CostElementDTO costElementDTO = null;
		List listResult = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try{
			listResult = new ArrayList(0);
			sql = new StringBuffer(SQL_SELECT_MISSED_COST_ELEMENTS);
			pstm = prepareSQL(sql.toString(), dsName);
			pstm.setInt(1, purchaseOrderVersionId);
			rs = executeQuery(pstm);
			while (rs.next()){
				costElementDTO = new CostElementDTO();
				costElementDTO.setCostElementID(rs.getInt(1));
				costElementDTO.setCostElementCode(rs.getString(2));
				costElementDTO.setCostElementDescription(rs.getString(3));
				listResult.add(costElementDTO);
			}
		}catch (PoolException poolException){
			CostElementException costElementException = null;
			poolException.printStackTrace(System.out);
			costElementException = new CostElementException (poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw costElementException;
		} catch(SQLException sqle){
			CostElementException costElementException = null;
			sqle.printStackTrace(System.out);
			costElementException = new CostElementException (sqle, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw costElementException;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}	
		
		return listResult;		
	}

}
